﻿using System.Linq;
using UniMana.Data;
using System.Data.Linq;
using System;

namespace UniMana.Data
{
    public static class Catalogue_DepartmentData
    {
        private static DBMain m_data = new DBMain();
        public static IQueryable GetList()
        {
            return m_data.m_DCReadOnly.Catalogue_Departments;
        }
        public static IQueryable GetDepartmentStudentManage()
        {
            var nQuery = m_data.m_DCReadOnly.Catalogue_Departments.Where(p => p.StudentManage == true);
            return nQuery;
        }
        public static IQueryable GetDepartmentSubjectManage()
        {
            var nQuery = m_data.m_DCReadOnly.Catalogue_Departments.Where(p => p.SubjectManage== true);
            return nQuery;
        }
        public static IQueryable GetDepartmentStudentManage(this Table<Catalogue_Department> nDepartment)
        {
            var nQuery = nDepartment.Where(p => p.StudentManage == true);
            return nQuery;
        }
        public static IQueryable GetByKey(int key)
        {
            return m_data.m_DCReadOnly.Catalogue_Departments.Where(p => p.Catalogue_DepartmentKey == key);
        }
        public static IQueryable GetDepartmentSubjectManage(this Table<Catalogue_Department> nDepartment)
        {
            var nQuery = nDepartment.Where(p => p.SubjectManage == true);
            return nQuery;
        }
        public static IQueryable GetDepartmentHaveMajor(
            this Table<Catalogue_Department> nDepartment, 
            Table<Catalogue_Major> nMajor)
        {
            var iQuery = from t in nDepartment
                         join t0 in nMajor on t.Catalogue_DepartmentKey equals t0.Catalogue_DepartmentKey
                         select t;
            return iQuery;
        }
        public static IQueryable
            GetDepartmentByMajor(
            this Table<Catalogue_Department> department, 
            Table<Catalogue_Major> major, 
            int majorkey)
        {
            var IQuery = from de in department
                         join mj in major on de.Catalogue_DepartmentKey equals mj.Catalogue_DepartmentKey
                         where
                           mj.Catalogue_MajorKey == majorkey
                         select de;
            return IQuery.Distinct();
        }

        #region [An]
        /// <summary>
        /// Lấy khoa quản lý sinh viêns
        /// </summary>
        /// <param name="nCatalogue_Department"></param>
        /// <returns></returns>

        public static IQueryable GetDepartmentManageStudent(this Table<Catalogue_Department> nCatalogue_Department)
        {
            var values = from t in nCatalogue_Department
                         where t.StudentManage == true
                         select t;
            return values;
        }

        /// <summary>
        /// Lấy khoa với bậc đào tạo
        /// </summary>
        /// <param name="nDepartment"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="nMajor"></param>
        /// <param name="m_iLevel"></param>
        /// <returns></returns>

        public static IQueryable GetDepartmentByLevel(this Table<Catalogue_Department> nCatalogue_Department,
            Table<Training_TrainingProgram> nTraining_TrainingProgram,
            Table<Catalogue_Major> nMajor,
            int m_iLevel)
        {
            var department = (from t in nMajor
                              join t0 in nCatalogue_Department on new { Catalogue_DepartmentKey = Convert.ToInt32(t.Catalogue_DepartmentKey) } equals new { Catalogue_DepartmentKey = t0.Catalogue_DepartmentKey }
                              join t1 in nTraining_TrainingProgram on new { Catalogue_MajorKey = t.Catalogue_MajorKey } equals new { Catalogue_MajorKey = Convert.ToInt32(t1.Catalogue_MajorKey) }
                              where
                                t1.Catalogue_LevelKey == m_iLevel
                              select new
                              {
                                  Catalogue_DepartmentKey = (System.Int32?)t.Catalogue_DepartmentKey,
                                  t0.Name
                              }).Distinct();
            return department.Distinct();
        }

        /// <summary>
        /// Lấy khoa với ngành
        /// </summary>
        /// <param name="nDepartment"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="nMajor"></param>
        /// <param name="m_iMajor"></param>
        /// <returns></returns>

        public static IQueryable GetDepartmentByMajor(this Table<Catalogue_Department> nCatalogue_Department,
            Table<Training_TrainingProgram> nTraining_TrainingProgram,
            Table<Catalogue_Major> nMajor,
            int m_iMajor)
        {
            var department = (from t in nMajor
                              join t0 in nCatalogue_Department on new { Catalogue_DepartmentKey = Convert.ToInt32(t.Catalogue_DepartmentKey) } equals new { Catalogue_DepartmentKey = t0.Catalogue_DepartmentKey }
                              join t1 in nTraining_TrainingProgram on new { Catalogue_MajorKey = t.Catalogue_MajorKey } equals new { Catalogue_MajorKey = Convert.ToInt32(t1.Catalogue_MajorKey) }
                              where
                                t1.Catalogue_MajorKey == m_iMajor
                              select new
                              {
                                  Catalogue_DepartmentKey = (System.Int32?)t.Catalogue_DepartmentKey,
                                  t0.Name
                              }).Distinct();
            return department.Distinct();
        }

        /// <summary>
        /// Lấy khoa với loại hình đào tạo
        /// </summary>
        /// <param name="nDepartment"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="nMajor"></param>
        /// <param name="m_iTrainingType"></param>
        /// <returns></returns>

        public static IQueryable GetDepartmentByTrainingType(this Table<Catalogue_Department> nCatalogue_Department,
            Table<Training_TrainingProgram> nTraining_TrainingProgram,
            Table<Catalogue_Major> nCatalogue_Major,
            int m_iTrainingType)
        {
            var department = (from t in nCatalogue_Major
                              join t0 in nCatalogue_Department on new { Catalogue_DepartmentKey = Convert.ToInt32(t.Catalogue_DepartmentKey) } equals new { Catalogue_DepartmentKey = t0.Catalogue_DepartmentKey }
                              join t1 in nTraining_TrainingProgram on new { Catalogue_MajorKey = t.Catalogue_MajorKey } equals new { Catalogue_MajorKey = Convert.ToInt32(t1.Catalogue_MajorKey) }
                              where
                                t1.Catalogue_TrainingTypeKey == m_iTrainingType
                              select new
                              {
                                  Catalogue_DepartmentKey = (System.Int32?)t.Catalogue_DepartmentKey,
                                  t0.Name
                              }).Distinct();
            return department.Distinct();
        }

        /// <summary>
        /// Lấy khoa với khóa học
        /// </summary>
        /// <param name="nDepartment"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="nMajor"></param>
        /// <param name="m_iCourse"></param>
        /// <returns></returns>

        public static IQueryable GetDepartmentByCourse(this Table<Catalogue_Department> nCatalogue_Department,
            Table<Training_TrainingProgram> nTraining_TrainingProgram,
            Table<Catalogue_Major> nCatalogue_Major,
            int m_iCourse)
        {
            var department = (from t in nCatalogue_Major
                              join t0 in nCatalogue_Department on new { Catalogue_DepartmentKey = Convert.ToInt32(t.Catalogue_DepartmentKey) } equals new { Catalogue_DepartmentKey = t0.Catalogue_DepartmentKey }
                              join t1 in nTraining_TrainingProgram on new { Catalogue_MajorKey = t.Catalogue_MajorKey } equals new { Catalogue_MajorKey = Convert.ToInt32(t1.Catalogue_MajorKey) }
                              where
                                t1.Catalogue_CourseKey == m_iCourse
                              select new
                              {
                                  Catalogue_DepartmentKey = (System.Int32?)t.Catalogue_DepartmentKey,
                                  t0.Name
                              }).Distinct();
            return department.Distinct();
        }

        /// <summary>
        /// Lấy khoa có trong chương trình đào tạo
        /// </summary>
        /// <param name="nDepartment"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="nMajor"></param>
        /// <returns></returns>

        public static IQueryable GetDepartmentInTrainingProgram(this Table<Catalogue_Department> nCatalogue_Department,
            Table<Training_TrainingProgram> nTraining_TrainingProgram,
            Table<Catalogue_Major> nCatalogue_Major)
        {
            var department = (from t in nTraining_TrainingProgram
                              join t0 in nCatalogue_Major on new { Catalogue_MajorKey = Convert.ToInt32(t.Catalogue_MajorKey) } equals new { Catalogue_MajorKey = t0.Catalogue_MajorKey }
                              join t1 in nCatalogue_Department on new { Catalogue_DepartmentKey = Convert.ToInt32(t0.Catalogue_DepartmentKey) } equals new { Catalogue_DepartmentKey = t1.Catalogue_DepartmentKey }
                              where
                                t1.StudentManage == true
                              select new
                              {
                                  Catalogue_DepartmentKey = (System.Int32?)t0.Catalogue_DepartmentKey,
                                  t1.Name,
                                  t1.StudentManage
                              }).Distinct();
            return department.Distinct();
        }

        /// <summary>
        /// Lấy khoa với bậc đào tạo, ngành, loại hình, khóa học
        /// </summary>
        /// <param name="nDepartment"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="nMajor"></param>
        /// <param name="lueCatalogueLevel"></param>
        /// <param name="lueCatalogueMajor"></param>
        /// <param name="lueCatalogueTrainingType"></param>
        /// <param name="lueCatalogueCourse"></param>
        /// <returns></returns>

        public static IQueryable GetDepartmentByLevelMajorTrainingTypeCourse(this Table<Catalogue_Department> nCatalogue_Department,
            Table<Training_TrainingProgram> nTraining_TrainingProgram,
            Table<Catalogue_Major> nCatalogue_Major,
            int m_iLevel,
            int m_iMajor,
            int m_iTrainingType,
            int m_iCourse)
        {
            var department = from t in nCatalogue_Department
                             join t0 in nCatalogue_Major on new { Catalogue_DepartmentKey = t.Catalogue_DepartmentKey } equals new { Catalogue_DepartmentKey = Convert.ToInt32(t0.Catalogue_DepartmentKey) }
                             join t1 in nTraining_TrainingProgram on new { Catalogue_MajorKey = t0.Catalogue_MajorKey } equals new { Catalogue_MajorKey = Convert.ToInt32(t1.Catalogue_MajorKey) }
                             where
                               t.StudentManage == true
                             select new
                             {
                                 Catalogue_DepartmentKey = (System.Int32?)t0.Catalogue_DepartmentKey,
                                 t1.Catalogue_LevelKey,
                                 Catalogue_MajorKey = (System.Int32?)t1.Catalogue_MajorKey,
                                 t1.Catalogue_TrainingTypeKey,
                                 t1.Catalogue_CourseKey,
                                 t.StudentManage,
                                 t.Name
                             };
            if (m_iLevel > 0)
            {
                department = department.Where(p => p.Catalogue_LevelKey == int.Parse(m_iLevel.ToString()));
            }
            if (m_iMajor > 0)
            {
                department = department.Where(p => p.Catalogue_MajorKey == int.Parse(m_iMajor.ToString()));
            }
            if (m_iTrainingType > 0)
            {
                department = department.Where(p => p.Catalogue_TrainingTypeKey == int.Parse(m_iTrainingType.ToString()));
            }
            if (m_iCourse > 0)
            {
                department = department.Where(p => p.Catalogue_CourseKey == int.Parse(m_iCourse.ToString()));
            }

            var a = from depart in department
                    select new
                    {
                        depart.Catalogue_DepartmentKey,
                        depart.Name
                    };

            return a.Distinct();
        }

        /// <summary>
        /// Lấy mã khoa với mã ngành
        /// </summary>
        /// <param name="nCatalogue_Department"></param>
        /// <param name="nCatalogue_Major"></param>
        /// <param name="m_iMajorKey"></param>
        /// <returns></returns>

        public static int ReturnDepartmentKeyWithMajorKey(this Table<Catalogue_Department> nCatalogue_Department,
            Table<Catalogue_Major> nCatalogue_Major,
            int m_iMajorKey)
        {
            var values = (from t in nCatalogue_Major
                          where t.Catalogue_MajorKey == m_iMajorKey
                          select new
                          {
                              t.Catalogue_DepartmentKey
                          }).Distinct().FirstOrDefault();
            return int.Parse(values.Catalogue_DepartmentKey.ToString());
        }

        /// <summary>
        /// Lấy tên  với mã
        /// </summary>
        /// <param name="nCatalogue_TrainingType"></param>
        /// <param name="m_iTypeKey"></param>
        /// <returns></returns>

        public static string ReturnNameWithKey(this Table<Catalogue_Department> nCatalogue_Department,
            int m_iKey)
        {
            var values = (from t in nCatalogue_Department
                          where t.Catalogue_DepartmentKey == m_iKey
                          select new
                          {
                              t.Name
                          }).Distinct().FirstOrDefault();
            return values.Name.ToString();
        }
        #endregion
    }
}
